from itertools import groupby

from odoo import api,fields, models
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT
from odoo.exceptions import UserError


class ProductStockReport(models.Model):
    _name = 's.product.stock.report'
    _description = "Product Stock Report"  # 成品入库报表
    _auto = False

    # base_mo = fields.Char('Base mo')
    product_id = fields.Many2one('product.product', string='Product')
    product_code = fields.Char(related='product_id.default_code', string='Product Code')
    # product_qty = fields.Float(string='Product Qty')
    stock_qty = fields.Float(string='Stock Qty')
    setl_meters = fields.Float(string='SET Meters', compute='_compute_setl_meters')
    t_date = fields.Date('Stock Date')

    @api.depends('product_id')
    def _compute_setl_meters(self):
        # 投料平米= 生产订单数量 / mrp_production.uom_value_ids(SET) * mrp_production.uom_value_ids(SETL) * mrp_production.uom_value_ids(SETL)
        for rec in self:
            set_value = 0
            setl_value = 0
            setw_value = 0
            if rec.product_id.uom_value_ids and rec.product_id.uom_value_ids.filtered(lambda a: a.uom_id.code == 'SET'):
                set_value = rec.product_id.uom_value_ids.filtered(lambda a: a.uom_id.code == 'SET')[0].uom_value
            if rec.product_id.uom_value_ids and rec.product_id.uom_value_ids.filtered(lambda a: a.uom_id.code == 'SETL'):
                setl_value = rec.product_id.uom_value_ids.filtered(lambda a: a.uom_id.code == 'SETL')[0].uom_value
            if rec.product_id.uom_value_ids and rec.product_id.uom_value_ids.filtered(lambda a: a.uom_id.code == 'SETW'):
                setw_value = rec.product_id.uom_value_ids.filtered(lambda a: a.uom_id.code == 'SETW')[0].uom_value
            if not set_value:
                rec.setl_meters = 0

            else:
                rec.setl_meters = rec.stock_qty / set_value * setl_value * setw_value / 1000000

    @property
    def _table_query(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        where = ''
        if start_date and end_date:
            where += f" AND sml.DATE >= '{start_date}' AND sml.DATE < '{end_date}'"

        sql = f"""
            with cte as (
                SELECT sml.product_id                                                                    product_id,
                       to_char(sml.date + '7 hour', 'YYYY-MM-DD')                                       done_date,
                       sum(sml.quantity)                                                                stock_qty,
                       min(sml.date) AS                                                                 t_date,
                       sum(sml.quantity) / puv1.uom_value * setl.uom_value * setw.uom_value / 1000000 setl_meters
                FROM stock_move_line sml
                         JOIN stock_move sm ON sml.move_id = sm.id
                         left join stock_lot on stock_lot.id = sml.lot_id
                         left join mrp_production smmp on stock_lot.s_production_id = smmp.id
                         left join stock_picking sp on sm.picking_id = sp.id
                         JOIN product_product pp1 ON pp1.ID = sml.product_id
                         JOIN product_template pt1 ON pp1.product_tmpl_id = pt1.ID
                         JOIN product_category pc1 ON pc1.ID = pt1.categ_id
                         left join stock_location sl on sml.location_dest_id = sl.id
                         LEFT JOIN product_uom_value setl
                                   ON setl.product_id = pt1.id AND setl.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETL')
                         LEFT JOIN product_uom_value setw
                                   ON setw.product_id = pt1.id AND setw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETW')
                         LEFT JOIN product_uom_value puv1
                                   ON puv1.product_id = pt1.id AND puv1.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SET')
                WHERE sml.state = 'done'
                  AND pc1.code = 'CHP'
                  and smmp.mo_type != 'W'
                  and sm.s_report_not_counted != true 
                  and sl.barcode = 'WH-STOCK-FINISHED-PRODUCT'
                  {where}
                group by sm.id, sml.product_id, sp.name, puv1.uom_value, setl.uom_value, setw.uom_value, sml.date
            )
                
            select row_number() OVER() AS id,product_id, done_date t_date, sum(stock_qty) stock_qty, sum(setl_meters)
            from cte
            group by product_id, done_date
        """
        return sql
